package com.example.projectmanagement.repository.study;

import com.example.projectmanagement.dto.study.CourseDistributionDto;
import com.example.projectmanagement.model.study.Course;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import java.util.List;

@Repository
public interface CourseRepository extends JpaRepository<Course, Long> {
    Page<Course> findByStatus(String status, Pageable pageable);
    Page<Course> findByCategory(String category, Pageable pageable);
    Page<Course> findByCategoryAndSubcategory(String category, String subcategory, Pageable pageable);
    Page<Course> findByTitleContainingIgnoreCase(String title, Pageable pageable);
    Page<Course> findByCreator_Id(Long creatorId, Pageable pageable);
    
    @Query("SELECT c FROM Course c WHERE c.status = 'ACTIVE' AND (:category IS NULL OR :category = '' OR c.category = :category) AND (:subcategory IS NULL OR :subcategory = '' OR c.subcategory = :subcategory) AND (:keyword IS NULL OR :keyword = '' OR LOWER(c.title) LIKE LOWER(CONCAT('%', :keyword, '%')) OR LOWER(c.description) LIKE LOWER(CONCAT('%', :keyword, '%')))")
    Page<Course> findPublishedCourses(@Param("category") String category, @Param("subcategory") String subcategory, @Param("keyword") String keyword, Pageable pageable);
    
    @Query("SELECT DISTINCT c.category FROM Course c WHERE c.status = 'ACTIVE'")
    List<String> findDistinctCategories();
    
    @Query("SELECT DISTINCT c.subcategory FROM Course c WHERE c.status = 'ACTIVE' AND c.category = :category")
    List<String> findDistinctSubcategoriesByCategory(@Param("category") String category);
    
    /**
     * 获取课程分类分布统计
     */
    @Query("SELECT new com.example.projectmanagement.dto.study.CourseDistributionDto(c.category, COUNT(c)) FROM Course c WHERE c.status = 'ACTIVE' GROUP BY c.category")
    List<CourseDistributionDto> getCourseDistribution();
}